package com.hoo.permission.sdk.server.dao.impl;

import com.hoo.common.model.Page;
import com.hoo.permission.sdk.server.dao.ISysUserDao;
import com.hoo.permission.sdk.server.domain.dto.SysUserDto;
import com.hoo.permission.sdk.server.domain.entity.SysUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 系统-用户表 Dao层 实现
 * @author 小韩工作室
 * @date 2020-06-28 16:51:32
 */
@Repository
public class SysUserDaoImpl implements ISysUserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public boolean add(SysUser entity){
        KeyHolder keyHolder = new GeneratedKeyHolder();
        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement ps = con.prepareStatement("INSERT INTO t_sys_user(nickname, username, password, salt, email, mobile, sex, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, entity.getNickname());
            ps.setString(2, entity.getUsername());
            ps.setString(3, entity.getPassword());
            ps.setString(4, entity.getSalt());
            ps.setString(5, entity.getEmail());
            ps.setString(6, entity.getMobile());
            ps.setString(7, entity.getSex());
            ps.setInt(8, entity.getStatus());
            return ps;
        };

        if(jdbcTemplate.update(preparedStatementCreator, keyHolder) > 0) {
            entity.setId(keyHolder.getKey().longValue());
            return true;
        }
        return false;
        // return jdbcTemplate.update("INSERT INTO t_sys_user(id, nickname, username, password, salt, email, mobile, sex, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)", entity.getId(), entity.getNickname(), entity.getUsername(), entity.getPassword(), entity.getSalt(), entity.getEmail(), entity.getMobile(), entity.getSex(), entity.getStatus()) > 0;
    }

    @Override
    public boolean update(SysUser entity) {
        return jdbcTemplate.update("UPDATE t_sys_user SET nickname = ? ,email = ? ,mobile = ? ,sex = ? ,status = ?  WHERE  id = ?", entity.getNickname(), entity.getEmail(), entity.getMobile(), entity.getSex(), entity.getStatus(), entity.getId()) > 0;
    }

    @Override
    public boolean updateStatus(Long id, int status) {
        return jdbcTemplate.update("UPDATE t_sys_user SET status = ?  WHERE  id = ?", status, id) > 0;
    }

    @Override
    public boolean delete(SysUser entity){
        return jdbcTemplate.update("DELETE FROM t_sys_user WHERE  id = ?", entity.getId()) > 0;
    }

    @Override
    public SysUser get(Serializable id) {
        List<SysUser> list = jdbcTemplate.query("SELECT a.id,a.nickname,a.username,a.password,a.salt,a.email,a.mobile,a.sex,a.status FROM t_sys_user a WHERE id = ? ", new Object[]{ id }, new BeanPropertyRowMapper<SysUser>(SysUser.class));
        if(list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public SysUser getByUsername(String username) {
        if (StringUtils.isEmpty(username)) {
            return null;
        }
        List<SysUser> list = jdbcTemplate.query("SELECT a.id,a.nickname,a.username,a.password,a.salt,a.email,a.mobile,a.sex,a.status FROM t_sys_user a WHERE username = ? ", new Object[]{ username }, new BeanPropertyRowMapper<SysUser>(SysUser.class));
        if(list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<SysUserDto> findAll(Map< String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT a.id,a.nickname,a.username,a.password,a.salt,a.email,a.mobile,a.sex,a.status, GROUP_CONCAT(DISTINCT b.role_id) AS role_ids, GROUP_CONCAT(DISTINCT c.resource_id) AS resource_ids FROM t_sys_user a LEFT JOIN t_sys_role_user b ON a.id = b.user_id\tLEFT JOIN t_sys_user_resource c ON a.id = c.user_id WHERE 1=1 ");
        List<Object> whereVals = new ArrayList<>();
        if(params.containsKey("ids")) {
            StringBuilder idsIn = new StringBuilder(" AND a.id in(");
            List<Long> ids = (List<Long>)params.get("ids");
            for(int i = 0, len = ids.size(); i < len; i++) {
                Long id = ids.get(i);
                idsIn.append(id).append(i == len - 1 ? "" : ",");
            }
            idsIn.append(")");
            if(ids.size() > 0) {
                sql.append(idsIn.toString());
            }
        }
        sql.append(" GROUP BY a.id");
        List<SysUserDto> list = jdbcTemplate.query(sql.toString(), whereVals.toArray(new Object[]{}), new BeanPropertyRowMapper<SysUserDto>(SysUserDto.class));
        if(list != null && list.size() > 0) {
            return list;
        }else{
            return new ArrayList<>();
        }
    }

    @Override
    public List<SysUserDto> query(Page page, Map<String, Object> params) {
        if(page == null) {
            return this.findAll(params);
        }

        List<Object> whereVals = new ArrayList<>();
        StringBuilder whereSql = new StringBuilder();

        if (params.get("status") != null) {
            whereSql.append(" AND a.status = ? ");
            whereVals.add(params.get("status"));
        }

        if (params.get("searchWord") != null) {
            whereSql.append(" AND (a.username LIKE ? OR a.nickname LIKE ?) ");
            whereVals.add("%" + params.get("searchWord") + "%");
            whereVals.add("%" + params.get("searchWord") + "%");
        }

        Long total = jdbcTemplate.queryForObject(new StringBuilder("SELECT COUNT(1) FROM t_sys_user a WHERE 1=1 ").append(whereSql).toString(), whereVals.toArray(new Object[]{}), Long.class);

        whereVals.add((page.getPageNo() - 1) * page.getLimit());
        whereVals.add(page.getLimit());
        List<SysUserDto> list = jdbcTemplate.query(new StringBuilder("SELECT a.id,a.nickname,a.username,a.password,a.salt,a.email,a.mobile,a.sex,a.status, GROUP_CONCAT(DISTINCT b.role_id) AS role_ids, GROUP_CONCAT(DISTINCT c.resource_id) AS resource_ids FROM t_sys_user a LEFT JOIN t_sys_role_user b ON a.id = b.user_id LEFT JOIN t_sys_user_resource c ON a.id = c.user_id WHERE 1=1 ").append(whereSql).append("GROUP BY a.id LIMIT ?,?").toString(), whereVals.toArray(new Object[]{}), new BeanPropertyRowMapper<>(SysUserDto.class));
        if(list != null && list.size() > 0) {
            page.setRecords(list);
        }
        page.setTotal(total);
        return list;
    }

    @Override
    public List<String> queryAllPerms(Long userId) {
        List<String> list = jdbcTemplate.query("SELECT m.permission from t_sys_role_user ur LEFT JOIN t_sys_role_resource rm on ur.role_id = rm.role_id LEFT JOIN t_sys_resource m on rm.resource_id = m.id WHERE ur.user_id = ? "
                + " UNION ALL "
                + "SELECT m.permission FROM t_sys_user_resource ur LEFT JOIN t_sys_resource m ON ur.resource_id = m.id WHERE ur.user_id = ?", new Object[]{userId, userId}, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getString(1);
            }
        });
        return list;
    }
}
